
/**
 * 数据库接口
 */

#include "data_base.h"
#include <stdio.h>
#include <string.h>
#include <memory.h>

//#pragma comment(lib,"libmysql.lib")
#include "mysql.h"

static int sql_flag = 0;

/*static const struct sql_storage_item sql_report_items_format[] = {
	// "vin, prot, mil, status, ready, svin, cin, iupr, fault_total, fault,",
	{"hard_id"   , "%s", ""},
	{"SN"   , "%s", ""},
};
static const int sql_report_items_size = sizeof(sql_report_items_format) / sizeof(sql_report_items_format[0]);*/

static char sql_field[1024 * 10];   // 字段
static char sql_value[1024 * 10];   // 值
static char sql_query[1024 * 10];   // SQL语句

int db_update_flag(void)
{
	return sql_flag;
}
int db_insert_item_format(struct sql_storage_item* const _items, const int _items_size, const char* const field, ...)
{
	int index = 0;
	struct sql_storage_item* item = NULL;
	for (index = 0; index < _items_size; index++)
	{
		item = &_items[index];
		if (0 == strcmp(field, item->field))
		{
			//sprintf(item->value, item->format, value);
			sql_flag = 1;
			//return 0;
			break;
		}
	}
	if (index < _items_size)
	{
		va_list ap;
		va_start(ap, field);
		//vprintf(__format, ap);
		//memset(text, 0, sizeof(text));
		//snprintf(text, sizeof (text), __format, ap);
		vsprintf(item->value, item->format, ap);
		va_end(ap);
		//printf("item->value:%s\n", item->value);
		return 0;
	}
	return -1;
}
// 存单个数据
int db_insert_item(struct sql_storage_item* const _items, const int _items_size, const char* const field, const double value)
{
	int index = 0;
	struct sql_storage_item* item = NULL;
	for (index = 0; index < _items_size; index++)
	{
		item = &_items[index];
		if (0 == strcmp(field, item->field))
		{
			sprintf(item->value, item->format, value);
			sql_flag = 1;
			return 0;
		}
	}
	return -1;
}
int db_insert_item_string(struct sql_storage_item* const _items, const int _items_size, const char* const field, const char* const string_value)
{
	int index = 0;
	struct sql_storage_item* item = NULL;
	for (index = 0; index < _items_size; index++)
	{
		item = &_items[index];
		if (0 == strcmp(field, item->field))
		{
#if 0
			sprintf(item->value, "\"%s\"", string_value);
#else
            unsigned int i=0;
            item->value[0] = '"';
            for(i=0; i<strlen(string_value); i++)
            {
                if('"'==string_value[i]) item->value[i+1] = '-';  // 替换 '\"'
                else item->value[i+1] = string_value[i];
            }
            item->value[i+1] = '"';
#endif
			sql_flag = 1;
			return 0;
		}
	}
	return -1;
}
int db_insert_item_int(struct sql_storage_item* const _items, const int _items_size, const char* const field, const int value)
{
	int index = 0;
	struct sql_storage_item* item = NULL;
	for (index = 0; index < _items_size; index++)
	{
		item = &_items[index];
		if (0 == strcmp(field, item->field))
		{
			sprintf(item->value, item->format, value);
			sql_flag = 1;
			return 0;
		}
	}
	return -1;
}
// 存入数据库
int db_insert_sql(struct sql_storage_item* const _items, const int _items_size, void*const _conn, const char* const tbl_name, const int update_flag)
{
	MYSQL* const conn = (MYSQL*)_conn;
	int index = 0;
	int ret = 0;
	char* _field = NULL;
	char* _value = NULL;
	struct sql_storage_item* item = NULL;
	if (0 == update_flag) return 0;
	memset(sql_field, 0, sizeof (sql_field));
	memset(sql_value, 0, sizeof (sql_value));
	memset(sql_query, 0, sizeof (sql_query));
	for (index = 0; index < _items_size - 1; index++)
	{
		item = &_items[index];
		_field = &sql_field[strlen(sql_field)];
		_value = &sql_value[strlen(sql_value)];
		sprintf(_field, "%s, ", item->field);
		sprintf(_value, "%s, ", item->value);
	}
	item = &_items[index];
	_field = &sql_field[strlen(sql_field)];
	_value = &sql_value[strlen(sql_value)];
	sprintf(_field, "%s", item->field);
	sprintf(_value, "%s", item->value);
	// "INSERT INTO tbl_obd_4g(vin,lat,lng) VALUES(%s,%f,%f)"
	// 合成 SQL 语句
	//sprintf(sql_query, "INSERT INTO tbl_obd_4g(%s) VALUES(%s)", sql_field, sql_value);
	sprintf(sql_query, "INSERT INTO %s(%s) VALUES(%s)", tbl_name, sql_field, sql_value);
	//printf("sql_query:%s \n", sql_query); fflush(stdout);
	ret = mysql_query(conn, sql_query);
	if (!ret)
	{
		//printf("Inserted %lu rows\n",(unsigned long)mysql_affected_rows(conn_ptr));
		return 0;
	}
	else
	{
		printf("Connect Erro:%d %s\n", mysql_errno(conn), mysql_error(conn));
	}
	return -1;
}
int db_format_init(const struct sql_storage_item* const _format, struct sql_storage_item* const _items, const int _items_size)
{
	memset(_items, 0, _items_size);
	memcpy(_items, _format, _items_size*sizeof(const struct sql_storage_item));
	memset(sql_field, 0, sizeof(sql_field));
	memset(sql_value, 0, sizeof(sql_value));
	memset(sql_query, 0, sizeof(sql_query));
	sql_flag = 0;
	return 0;
}
int db_main(void)
{
	MYSQL conn;
	mysql_init(&conn);  //如果传入的指针为空，会分配一个MYSQL的结构体，并由mysql_close()释放掉
	//if( mysql_real_connect(&conn, "localhost", "root", "123456y", "hniois", 0, NULL, 0) == NULL )
	if (mysql_real_connect(&conn, "localhost", "xcwy", "xcwy_data", "xcwy", 3306, NULL, 0) == NULL)
	{
		printf("Error %u: %s\n", mysql_errno(&conn), mysql_error(&conn)); fflush(stdout);
		return 0;// exit(1);
	}
	/* int mysql_query(MYSQL *mysql, const char *stmt_str) */
	/* Zero for success. Nonzero if an error occurred. */
	if (mysql_query(&conn, "select * from tbl_obd_4g"))
	{
		printf("Error %u: %s\n", mysql_errno(&conn), mysql_error(&conn));
		exit(1);
	}

	MYSQL_RES* result;
	MYSQL_ROW row;
	unsigned long* lengths;
	MYSQL_FIELD* fields;

	result = mysql_use_result(&conn);  //内部会分配MYSQL_RES结构体，由mysql_free_result()释放掉

	int num_fields = mysql_num_fields(result);       //结果集有多少列
	printf("mysql_use_result列：%d\n", num_fields);

	/* fields是MYSQL_FIELD结构体数组的首地址，MYSQL_FIELD结构体的name项是列名 */
	fields = mysql_fetch_fields(result);      //估计fields指向的是静态局部变量
	int i = 0;
	for (i = 0; i < num_fields; i++)
	{
		printf("Field %u is %s\n", i, fields[i].name);
	}
#if 1
	/* 实际检索来自服务器的行，检索结果集的下一行;直至为返回NULL，检索完毕 */
	while (NULL != (row = mysql_fetch_row(result)))
	{
		my_ulonglong num_rows = mysql_num_rows(result);  //返回结果集的行号
		printf("mysql_use_result行号: %lu\n", (unsigned long)num_rows);

		/* 显示该行号中的数据 */
		for (i = 0; i < num_fields; i++)
		{
			printf("values:%s   \n", row[i]);
		}

		/* 显示该行中的每一列数据长度 */
		lengths = mysql_fetch_lengths(result);      //生成一维int数组(静态局部变量)，把首地址给lengths；储存每一列的长度
		for (i = 0; i < num_fields; i++)
		{
			printf("Column %u is %lu bytes in length.\n", i, lengths[i]);
		}
	}
#endif
	mysql_free_result(result);
	mysql_close(&conn);
	return 0;
}

/*  保存硬件ID和序列号到数据库  */
int db_insert_test(const char hard_id[], const char sn[])
{
	static struct sql_storage_item sql_report_items[128];
	static const struct sql_storage_item sql_report_items_format[] = {
		// "vin, prot, mil, status, ready, svin, cin, iupr, fault_total, fault,",
		{"hard_id"   , "%s", ""},
		{"SN"   , "%s", ""},
	};
	MYSQL conn;
	mysql_init(&conn);  //如果传入的指针为空，会分配一个MYSQL的结构体，并由mysql_close()释放掉
	//if( mysql_real_connect(&conn, "localhost", "root", "123456y", "hniois", 0, NULL, 0) == NULL )
	if (mysql_real_connect(&conn, "localhost", "xcwy", "xcwy_data", "xcwy", 3306, NULL, 0) == NULL)
	{
		printf("Error %u: %s\n", mysql_errno(&conn), mysql_error(&conn));
		return -1;// exit(1);
	}
	db_format_init(sql_report_items_format, sql_report_items, 2);
	db_insert_item_string(sql_report_items, 2, "hard_id", hard_id);
	db_insert_item_string(sql_report_items, 2, "SN", sn);
	db_insert_sql(sql_report_items, 2, &conn, "tbl_obd_4g", sql_flag);
	mysql_close(&conn);
	return 0;
}





